package com.hellogood.yaohuo.server.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.shared.data.CallDTO;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.exception.OperationException;

/**
 * 
 * @author renxh_cn
 */
public abstract class DBHelper {

	Logger log = Logger.getLogger(this.getClass());

	public int execute(String sql) throws DatabaseException {
		int result = 0;
		PreparedStatement ps = null;
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			result = ps.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
			log.error(sql);
			log.error(ex.getMessage());
			throw new DatabaseException(ex.getMessage());	
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
					log.error(sql);
					log.error(ex.getMessage());
					throw new DatabaseException(ex.getMessage());
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return result;
	}

	public int[] executeBatch(List<String> sqls) throws DatabaseException {
		int[] result = new int[sqls.size()];
		Statement stmt = null;
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = DBConnection.getInstance().getConnection();
		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < sqls.size(); i++) {
				System.out.println(sqls.get(i));
				stmt.addBatch(sqls.get(i));
			}
			result = stmt.executeBatch();
			conn.commit();
		} catch (SQLException ex) {
			ex.printStackTrace();
			for (String sql : sqls) {
				log.error(sql);
			}
			log.error(ex.getMessage());
			throw new DatabaseException(ex.getMessage());
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
					log.error(ex.getMessage());
				}
			}
			if (conn != null) {
				try {
					conn.setAutoCommit(true);
				} catch (SQLException ex) {
					ex.printStackTrace();
					log.error(ex.getMessage());
					throw new DatabaseException(ex.getMessage());
				}
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return result;
	}

	protected List<?> query(String sql) {
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = DBConnection.getInstance().getConnection();
		List<?> list = new ArrayList<Object>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			list = initializeObject(rs);
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(sql);
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return list;
	}

	public List<?> query(String sql, Object[] args) {
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = DBConnection.getInstance().getConnection();
		List<?> objs = new ArrayList<Object>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			if (args != null && args.length != 0) {
				for (int i = 0; i < args.length; i++) {
					ps.setObject(i + 1, args[i]);
				}
			}
			rs = ps.executeQuery();
			objs = initializeObject(rs);
		} catch (Exception e) {
			e.printStackTrace();
			log.error(sql);
			log.error(e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return objs;
	}

	public String getLastBillNo(String sql, String billName) {
		String billNo = null;
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = DBConnection.getInstance().getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				billNo = rs.getString(billName);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(sql);
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return billNo;
	}

	public void callProcedure(CallDTO dto) {
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = DBConnection.getInstance().getConnection();
		CallableStatement proc = null;
		try {
			proc = conn.prepareCall(dto.getCall()); // 调用存储过程
			proc.setString(1, dto.getBill());
			proc.setString(2, dto.getHxdbName());

			proc.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(dto.getCall());
			log.error(e.getMessage());
		}
		if (proc != null) {
			try {
				proc.close();
			} catch (SQLException e) {
				log.error(e.getMessage());
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException ex) {
				ex.printStackTrace();
				log.error(ex.getMessage());
			}
			// ConnectionPool.getInstance().release(conn);
		}
	}

	public abstract List<?> initializeObject(ResultSet rs) throws SQLException;
}
